--    Program: 		CAPPOIMP_POSITION_TL.sql
--    Description:  Import PO POSITION information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   Kenny Leung
--    Date:         27-Jan-2006

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp			varchar2(2000);
   
   l_position_id            number;
                    		                 		
   cursor cur_hr_all_positions_f is
      select * from CRST_HR_ALL_POSITIONS_F;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_hr_all_positions_f loop   	
     l_err_msg_tmp := '';
     
     l_position_id := null; 	
     select HR_ALL_POSITIONS_F_S.NEXTVAL
   	 into   l_position_id
   	 from   dual;  
     
     begin
       -- import data
       insert into HR_ALL_POSITIONS_F
       (
            POSITION_ID                 , 
			EFFECTIVE_START_DATE        , 
			EFFECTIVE_END_DATE          , 
			AVAILABILITY_STATUS_ID      , 
			BUSINESS_GROUP_ID           , 
			ENTRY_GRADE_ID              , 
			ENTRY_GRADE_RULE_ID         , 
			ENTRY_STEP_ID               , 
			JOB_ID                      , 
			LOCATION_ID                 , 
			ORGANIZATION_ID             , 
			PAY_BASIS_ID                , 
			PAY_FREQ_PAYROLL_ID         , 
			POSITION_DEFINITION_ID      , 
			POSITION_TRANSACTION_ID     , 
			PRIOR_POSITION_ID           , 
			RELIEF_POSITION_ID          , 
			SUCCESSOR_POSITION_ID       , 
			SUPERVISOR_ID               , 
			SUPERVISOR_POSITION_ID      , 
			AMENDMENT_DATE              , 
			AMENDMENT_RECOMMENDATION    , 
			AMENDMENT_REF_NUMBER        , 
			AVAIL_STATUS_PROP_END_DATE  , 
			BARGAINING_UNIT_CD          , 
			COMMENTS                    , 
			COPIED_TO_OLD_TABLE_FLAG    , 
			CURRENT_JOB_PROP_END_DATE   , 
			CURRENT_ORG_PROP_END_DATE   , 
			DATE_EFFECTIVE              , 
			DATE_END                    , 
			EARLIEST_HIRE_DATE          , 
			FILL_BY_DATE                , 
			FREQUENCY                   , 
			FTE                         , 
			MAX_PERSONS                 , 
			NAME                        , 
			OVERLAP_PERIOD              , 
			OVERLAP_UNIT_CD             , 
			PAY_TERM_END_DAY_CD         , 
			PAY_TERM_END_MONTH_CD       , 
			PERMANENT_TEMPORARY_FLAG    , 
			PERMIT_RECRUITMENT_FLAG     , 
			POSITION_TYPE               , 
			POSTING_DESCRIPTION         , 
			PROBATION_PERIOD            , 
			PROBATION_PERIOD_UNIT_CD    , 
			PROPOSED_FTE_FOR_LAYOFF     , 
			PROPOSED_DATE_FOR_LAYOFF    , 
			REPLACEMENT_REQUIRED_FLAG   , 
			REVIEW_FLAG                 , 
			SEASONAL_FLAG               , 
			SECURITY_REQUIREMENTS       , 
			STATUS                      , 
			TERM_START_DAY_CD           , 
			TERM_START_MONTH_CD         , 
			TIME_NORMAL_FINISH          , 
			TIME_NORMAL_START           , 
			UPDATE_SOURCE_CD            , 
			WORKING_HOURS               , 
			WORKS_COUNCIL_APPROVAL_FLAG , 
			WORK_PERIOD_TYPE_CD         , 
			WORK_TERM_END_DAY_CD        , 
			WORK_TERM_END_MONTH_CD      , 
			INFORMATION1                , 
			INFORMATION2                , 
			INFORMATION3                , 
			INFORMATION4                , 
			INFORMATION5                , 
			INFORMATION6                , 
			INFORMATION7                , 
			INFORMATION8                , 
			INFORMATION9                , 
			INFORMATION10               , 
			INFORMATION11               , 
			INFORMATION12               , 
			INFORMATION13               , 
			INFORMATION14               , 
			INFORMATION15               , 
			INFORMATION16               , 
			INFORMATION17               , 
			INFORMATION18               , 
			INFORMATION19               , 
			INFORMATION20               , 
			INFORMATION21               , 
			INFORMATION22               , 
			INFORMATION23               , 
			INFORMATION24               , 
			INFORMATION25               , 
			INFORMATION26               , 
			INFORMATION27               , 
			INFORMATION28               , 
			INFORMATION29               , 
			INFORMATION30               , 
			INFORMATION_CATEGORY        , 
			ATTRIBUTE1                  , 
			ATTRIBUTE2                  , 
			ATTRIBUTE3                  , 
			ATTRIBUTE4                  , 
			ATTRIBUTE5                  , 
			ATTRIBUTE6                  , 
			ATTRIBUTE7                  , 
			ATTRIBUTE8                  , 
			ATTRIBUTE9                  , 
			ATTRIBUTE10                 , 
			ATTRIBUTE11                 , 
			ATTRIBUTE12                 , 
			ATTRIBUTE13                 , 
			ATTRIBUTE14                 , 
			ATTRIBUTE15                 , 
			ATTRIBUTE16                 , 
			ATTRIBUTE17                 , 
			ATTRIBUTE18                 , 
			ATTRIBUTE19                 , 
			ATTRIBUTE20                 , 
			ATTRIBUTE21                 , 
			ATTRIBUTE22                 , 
			ATTRIBUTE23                 , 
			ATTRIBUTE24                 , 
			ATTRIBUTE25                 , 
			ATTRIBUTE26                 , 
			ATTRIBUTE27                 , 
			ATTRIBUTE28                 , 
			ATTRIBUTE29                 , 
			ATTRIBUTE30                 , 
			ATTRIBUTE_CATEGORY          , 
			REQUEST_ID                  , 
			PROGRAM_APPLICATION_ID      , 
			PROGRAM_ID                  , 
			PROGRAM_UPDATE_DATE         , 
			CREATED_BY                  , 
			CREATION_DATE               , 
			LAST_UPDATED_BY             , 
			LAST_UPDATE_DATE            , 
			LAST_UPDATE_LOGIN           , 
			OBJECT_VERSION_NUMBER       
       )
       values
       (
            l_position_id        ,
            CUR.EFFECTIVE_START_DATE        , 
			CUR.EFFECTIVE_END_DATE          , 
			CUR.AVAILABILITY_STATUS_ID      , 
			CAPBRIMP_MAPPING.GetBGMapping(CUR.BUSINESS_GROUP_ID), 
			CUR.ENTRY_GRADE_ID              , 
			CUR.ENTRY_GRADE_RULE_ID         , 
			CUR.ENTRY_STEP_ID               , 
			CAPPOIMP_MAPPING.GetJOBIDMapping(CUR.JOB_ID), 
			CUR.LOCATION_ID                 , 
			CAPBRIMP_MAPPING.GetOrgMapping(CUR.ORGANIZATION_ID), 
			CUR.PAY_BASIS_ID                , 
			CUR.PAY_FREQ_PAYROLL_ID         , 
			CUR.POSITION_DEFINITION_ID      , 
			CUR.POSITION_TRANSACTION_ID     , 
			CUR.PRIOR_POSITION_ID           , 
			CUR.RELIEF_POSITION_ID          , 
			CUR.SUCCESSOR_POSITION_ID       , 
			CUR.SUPERVISOR_ID               , 
			CUR.SUPERVISOR_POSITION_ID      , 
			CUR.AMENDMENT_DATE              , 
			CUR.AMENDMENT_RECOMMENDATION    , 
			CUR.AMENDMENT_REF_NUMBER        , 
			CUR.AVAIL_STATUS_PROP_END_DATE  , 
			CUR.BARGAINING_UNIT_CD          , 
			CUR.COMMENTS                    , 
			CUR.COPIED_TO_OLD_TABLE_FLAG    , 
			CUR.CURRENT_JOB_PROP_END_DATE   , 
			CUR.CURRENT_ORG_PROP_END_DATE   , 
			CUR.DATE_EFFECTIVE              , 
			CUR.DATE_END                    , 
			CUR.EARLIEST_HIRE_DATE          , 
			CUR.FILL_BY_DATE                , 
			CUR.FREQUENCY                   , 
			CUR.FTE                         , 
			CUR.MAX_PERSONS                 , 
			CUR.NAME                        , 
			CUR.OVERLAP_PERIOD              , 
			CUR.OVERLAP_UNIT_CD             , 
			CUR.PAY_TERM_END_DAY_CD         , 
			CUR.PAY_TERM_END_MONTH_CD       , 
			CUR.PERMANENT_TEMPORARY_FLAG    , 
			CUR.PERMIT_RECRUITMENT_FLAG     , 
			CUR.POSITION_TYPE               , 
			CUR.POSTING_DESCRIPTION         , 
			CUR.PROBATION_PERIOD            , 
			CUR.PROBATION_PERIOD_UNIT_CD    , 
			CUR.PROPOSED_FTE_FOR_LAYOFF     , 
			CUR.PROPOSED_DATE_FOR_LAYOFF    , 
			CUR.REPLACEMENT_REQUIRED_FLAG   , 
			CUR.REVIEW_FLAG                 , 
			CUR.SEASONAL_FLAG               , 
			CUR.SECURITY_REQUIREMENTS       , 
			CUR.STATUS                      , 
			CUR.TERM_START_DAY_CD           , 
			CUR.TERM_START_MONTH_CD         , 
			CUR.TIME_NORMAL_FINISH          , 
			CUR.TIME_NORMAL_START           , 
			CUR.UPDATE_SOURCE_CD            , 
			CUR.WORKING_HOURS               , 
			CUR.WORKS_COUNCIL_APPROVAL_FLAG , 
			CUR.WORK_PERIOD_TYPE_CD         , 
			CUR.WORK_TERM_END_DAY_CD        , 
			CUR.WORK_TERM_END_MONTH_CD      , 
			CUR.INFORMATION1                , 
			CUR.INFORMATION2                , 
			CUR.INFORMATION3                , 
			CUR.INFORMATION4                , 
			CUR.INFORMATION5                , 
			CUR.INFORMATION6                , 
			CUR.INFORMATION7                , 
			CUR.INFORMATION8                , 
			CUR.INFORMATION9                , 
			CUR.INFORMATION10               , 
			CUR.INFORMATION11               , 
			CUR.INFORMATION12               , 
			CUR.INFORMATION13               , 
			CUR.INFORMATION14               , 
			CUR.INFORMATION15               , 
			CUR.INFORMATION16               , 
			CUR.INFORMATION17               , 
			CUR.INFORMATION18               , 
			CUR.INFORMATION19               , 
			CUR.INFORMATION20               , 
			CUR.INFORMATION21               , 
			CUR.INFORMATION22               , 
			CUR.INFORMATION23               , 
			CUR.INFORMATION24               , 
			CUR.INFORMATION25               , 
			CUR.INFORMATION26               , 
			CUR.INFORMATION27               , 
			CUR.INFORMATION28               , 
			CUR.INFORMATION29               , 
			CUR.INFORMATION30               , 
			CUR.INFORMATION_CATEGORY        , 
			CUR.ATTRIBUTE1                  , 
			CUR.ATTRIBUTE2                  , 
			CUR.ATTRIBUTE3                  , 
			CUR.ATTRIBUTE4                  , 
			CUR.ATTRIBUTE5                  , 
			CUR.ATTRIBUTE6                  , 
			CUR.ATTRIBUTE7                  , 
			CUR.ATTRIBUTE8                  , 
			CUR.ATTRIBUTE9                  , 
			CUR.ATTRIBUTE10                 , 
			CUR.ATTRIBUTE11                 , 
			CUR.ATTRIBUTE12                 , 
			CUR.ATTRIBUTE13                 , 
			CUR.ATTRIBUTE14                 , 
			CUR.ATTRIBUTE15                 , 
			CUR.ATTRIBUTE16                 , 
			CUR.ATTRIBUTE17                 , 
			CUR.ATTRIBUTE18                 , 
			CUR.ATTRIBUTE19                 , 
			CUR.ATTRIBUTE20                 , 
			CUR.ATTRIBUTE21                 , 
			CUR.ATTRIBUTE22                 , 
			CUR.ATTRIBUTE23                 , 
			CUR.ATTRIBUTE24                 , 
			CUR.ATTRIBUTE25                 , 
			CUR.ATTRIBUTE26                 , 
			CUR.ATTRIBUTE27                 , 
			CUR.ATTRIBUTE28                 , 
			CUR.ATTRIBUTE29                 , 
			CUR.ATTRIBUTE30                 , 
			CUR.ATTRIBUTE_CATEGORY          , 
			CUR.REQUEST_ID                  , 
			CUR.PROGRAM_APPLICATION_ID      , 
			CUR.PROGRAM_ID                  , 
			CUR.PROGRAM_UPDATE_DATE         , 
			CUR.CREATED_BY                  , 
			CUR.CREATION_DATE               , 
			CUR.LAST_UPDATED_BY             , 
			CUR.LAST_UPDATE_DATE            , 
			CUR.LAST_UPDATE_LOGIN           , 
			CUR.OBJECT_VERSION_NUMBER    
       );  
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp IS NULL then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;
   end loop;

   if l_rejected_count = 0 then
   	 commit;
   else
     rollback;
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPPOIMP_POSITION_TL', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
